![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
Parallel Query Operation With traditional queries such as table scans, the server process reads the data sequentially (see Figure 10.8). Much of the time spent in this query is spent waiting for I/Os to complete.
A parallel query splits the query into several different pieces, each one processed by a different server process. These processes are called query servers. The query servers are dispatched by a process known as the query coordinator. The query coordinator dispatches the query servers and coordinates the results from all the servers to send back to the user. The result of this arrangement is that many smaller table scans take place under the hood (transparent to the user). From the users standpoint, it is simply a much faster table scan. Figure 10.9 shows a parallel query.
The query coordinator is given an SQL statement and a degree of parallelism and is responsible for dividing the query among the query servers and integrating the individual results into one result. The degree of parallelism is the number of query servers assigned to the particular query. The Oracle server can make parallel the following operations:
Each of these operations have requirements that determine how the query is parallelized. The performance achieved by the parallel query is determined both by the size of the data to be accessed and the degree of parallelism achieved. How the query is parallelized (if at all) is determined by the query coordinator. The decision is made in this order:
The degree of parallelism is determined using the following precedence:
Regardless of what these values are set to, the number of query servers cannot exceed the number of query servers available in the query server pool. This number is specified by the Oracle initialization parameter, PARALLEL_MAX_SERVERS. Hints for the degree of parallelism are set within a comment string in the SQL statement. The syntax of this comment is as follows: PARALLEL ( alias_or_tablename , [ integer/DEFAULT ] [ , integer/DEFAULT ] ) The PARALLEL hint specifies the table or alias being scanned, followed by a value for the number of query servers to be used (or the DEFAULT). The final optional value specifies how the table is to be split among different instances of a parallel server. These hints are described in detail in Chapter 30, Using Hints. Here is an example using the DOGS table introduced earlier in this chapter: SELECT /*+ FULL(dogs) PARALLEL(dogs, 4) */ dogname FROM dogs; When you add the FULL and PARALLEL hints to this statement, the Oracle optimizer creates an execution plan that uses a full-table scan. Furthermore, this tablescan is executed with a parallel degree of 4 if the query servers are available. This statement overrides both the degree of parallelism specified in the table definition and the default Oracle initialization parameters. The hint NOPARALLEL disables parallel scanning of a table and overrides the specified degree of parallelism. The NOPARALLEL hint has the following syntax: NOPARALLEL ( alias_or_tablename ) Parallel Query Tuning Parallel query operations can be very effective on multiprocessor or parallel-processing computers; they can also be effective on uniprocessor systems where much of the time is spent waiting for I/O operations to complete. Systems with sufficient I/O bandwidthand especially systems with disk arraysbenefit from parallel query operations. If your system is typically processing at 100 percent of your CPU utilization and you have a small number of disk drives, you will probably not benefit from parallel query operations. If your system is extremely memory limited, you also will probably not benefit from parallel query operations. The two areas that can be tuned for parallel queries are I/O and parallel servers. By properly configuring your data files, you can help parallel queries be more effective.
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Read EarthWeb's privacy statement. |